﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using WF_QLThuVien.Utilities;
using WF_QLThuVien.Entities.PhieuMuon;
using WF_QLThuVien.Entities.Sach;

namespace WF_QLThuVien.DataLayer.PhieuMuon
{
    public class CCTPhieuMuonDAL : CConnection
    {
        OleDbCommand cmd = new OleDbCommand();
        public void KTConnect()
        {
            try
            {
                Connection();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public bool themCTPhieuMuon(CCTPhieuMuon ctpm)
        {
            KTConnect();
            try
            {
                //Kiểm tra tồn tại mã phiếu mượn + mã sách
                string phieuMuonID = ctpm.PhieuMuonID;
                cmd = new OleDbCommand("SELECT * FROM PhieuMuon WHERE PhieuMuonID='"+phieuMuonID+"'",dataConnection);
                OleDbDataReader dataReader = cmd.ExecuteReader();
                string sachID = ctpm.Sach.SachID;
                cmd = new OleDbCommand("SELECT * FROM Sach WHERE SachID='"+sachID+"'",dataConnection);
                OleDbDataReader dataReader1 = cmd.ExecuteReader();
                if (dataReader == null || dataReader1 == null)
                {
                    throw new Exception("Không Tồn Tại Sách hoặc Phiếu Mượn - CCTPhieuMuonDAL");
                }
                else
                {
                    cmd = new OleDbCommand("INSERT INTO ChiTietPhieuMuon VALUES('" + ctpm.PhieuMuonID + "', '" + ctpm.Sach.SachID + "', '" + ctpm.TrangThai + "', #" + ctpm.NgayTra + "#, " + ctpm.SoLanGiaHan + ")", dataConnection);
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi thêm Chi Tiết Phiếu Mượn - CCTPhieuMuon" + ex.ToString());
            }
        }

        #region: Tìm kiếm
        //Tìm chi tiết phiếu mượn khi có PhieuMuonID
        public CCTPhieuMuon timCTPMByPhieuMuonID(string maPhieuMuon)
        {
            KTConnect();
            try
            {
                CCTPhieuMuon ctpm = new CCTPhieuMuon();
                cmd = new OleDbCommand("SELECT * FROM ChiTietPhieuMuon WHERE PhieuMuonID='" + maPhieuMuon + "'",dataConnection);
                OleDbDataReader dataReader = cmd.ExecuteReader();
                if (dataReader == null)
                {
                    throw new Exception("Không Tìm Thấy CTPM - CCTPhieuMuonDAL");
                }
                else
                {
                    while (dataReader.Read())
                    {
                        ctpm.PhieuMuonID = dataReader.GetValue(0).ToString();
                        string sachID = dataReader.GetValue(1).ToString();
                        CSach s=new CSach();
                        cmd = new OleDbCommand("SELECT * FROM Sach WHERE SachID='" + sachID + "'", dataConnection);
                        OleDbDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            s.SachID = reader.GetValue(0).ToString();
                            s.DauSachID = reader.GetValue(1).ToString();
                            s.TrangThai = reader.GetValue(2).ToString();
                            s.GhiChu = reader.GetValue(3).ToString();
                        }
                        ctpm.Sach = s;
                        ctpm.TrangThai = dataReader.GetValue(2).ToString();
                        ctpm.NgayTra = Convert.ToDateTime(dataReader.GetValue(3)); //Là hạn trả
                        ctpm.SoLanGiaHan = Convert.ToInt32(dataReader.GetValue(4));
                    }
                    return ctpm;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Tìm CTPhieuMuon - CCTPhieuMuonDAL\n" + ex.ToString());
            }
        }

        //Tìm ArrayList CTPM
        public List<CCTPhieuMuon> timListCTPMByPhieuMuonID(string maPhieuMuon)
        {
            KTConnect();
            try
            {
                List<CCTPhieuMuon> arrCTPM = new List<CCTPhieuMuon>();
                CCTPhieuMuon ctpm = new CCTPhieuMuon();
                cmd = new OleDbCommand("SELECT * FROM ChiTietPhieuMuon WHERE PhieuMuonID='" + maPhieuMuon + "'", dataConnection);
                OleDbDataReader dataReader = cmd.ExecuteReader();
                if (dataReader == null)
                {
                    throw new Exception("Không Tìm Thấy CTPM - CCTPhieuMuonDAL");
                }
                else
                {
                    while (dataReader.Read())
                    {
                        ctpm.PhieuMuonID = dataReader.GetValue(0).ToString();
                        string sachID = dataReader.GetValue(1).ToString();
                        CSach s = new CSach();
                        cmd = new OleDbCommand("SELECT * FROM Sach WHERE SachID='" + sachID + "'", dataConnection);
                        OleDbDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            s.SachID = reader.GetValue(0).ToString();
                            s.DauSachID = reader.GetValue(1).ToString();
                            s.TrangThai = reader.GetValue(2).ToString();
                            s.GhiChu = reader.GetValue(3).ToString();
                        }
                        ctpm.Sach = s;
                        ctpm.TrangThai = dataReader.GetValue(2).ToString();
                        ctpm.NgayTra = Convert.ToDateTime(dataReader.GetValue(3)); //Là hạn trả
                        ctpm.SoLanGiaHan = Convert.ToInt32(dataReader.GetValue(4));
                        arrCTPM.Add(ctpm);
                    }
                    return arrCTPM;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Tìm CTPhieuMuon - CCTPhieuMuonDAL\n" + ex.ToString());
            }
        }
        //timListCTPMByPhieuMuonID
        #endregion

        #region: UPDATE
        public bool updateSoLanGiaHan(string maSach)
        {
            KTConnect();
            try
            {
                CSach s = new CSach();
                CCTPhieuMuon ctpm = new CCTPhieuMuon();
                cmd = new OleDbCommand("SELECT * FROM ChiTietPhieuMuon WHERE SachID='" + maSach + "'", dataConnection);
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ctpm = new CCTPhieuMuon();
                    ctpm.PhieuMuonID = reader.GetValue(0).ToString();
                    cmd = new OleDbCommand("SELECT * FROM Sach WHERE SachID='" + maSach + "'", dataConnection);
                    OleDbDataReader readerSach = cmd.ExecuteReader();
                    while (readerSach.Read())
                    {
                        s = new CSach();
                        s.SachID = readerSach.GetValue(0).ToString();
                        s.DauSachID = readerSach.GetValue(1).ToString();
                        s.TrangThai = readerSach.GetValue(2).ToString();
                        s.GhiChu = readerSach.GetValue(3).ToString();
                    }
                    ctpm.Sach = s;
                    ctpm.TrangThai = reader.GetValue(2).ToString();
                    ctpm.NgayTra = Convert.ToDateTime(reader.GetValue(3));
                    ctpm.SoLanGiaHan = Convert.ToInt32(reader.GetValue(4));
                }

                cmd = new OleDbCommand("UPDATE ChiTietPhieuMuon SET SoLanGiaHan=" + (ctpm.SoLanGiaHan + 1) + " WHERE SachID='" + maSach + "'", dataConnection);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Update Số Lần Gia Hạn - CCTPhieuMmuonDAL\n" + ex.ToString());
            }
        }
        #endregion

        #region: TÌM
        public int TimSoLanGiaHanByMaSach(string maSach)
        {
            KTConnect();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM ChiTietPhieuMuon WHERE SachID='" + maSach + "'", dataConnection);
                OleDbDataReader reader = cmd.ExecuteReader();
                if (reader == null)
                {
                    throw new Exception("Không Tìm Thấy Mã Sách");
                }
                else
                {
                    CSach s = new CSach();
                    CCTPhieuMuon ctpm = new CCTPhieuMuon();
                    while (reader.Read())
                    {
                        ctpm.PhieuMuonID = reader.GetValue(0).ToString();
                        cmd = new OleDbCommand("SELECT * FROM Sach WHERE SachID='" + maSach + "'", dataConnection);
                        OleDbDataReader readerSach = cmd.ExecuteReader();
                        while (readerSach.Read())
                        {
                            s = new CSach();
                            s.SachID = readerSach.GetValue(0).ToString();
                            s.DauSachID = readerSach.GetValue(1).ToString();
                            s.TrangThai = readerSach.GetValue(2).ToString();
                            s.GhiChu = readerSach.GetValue(3).ToString();
                        }
                        ctpm.Sach = s;
                        ctpm.TrangThai = reader.GetValue(2).ToString();
                        ctpm.NgayTra = Convert.ToDateTime(reader.GetValue(3));
                        ctpm.SoLanGiaHan = Convert.ToInt32(reader.GetValue(4));
                    }
                    return ctpm.SoLanGiaHan;
                } 
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Tìm Chi Tiết Phiếu Mượn!" + ex.ToString());
            }
        }

        public int TimSoCTPMByMaDocGia(string maDocGia)
        {
            KTConnect();
            try
            {
                CPhieuMuon pm = new CPhieuMuon();
                cmd = new OleDbCommand("SELECT * FROM PhieuMuon WHERE DocGiaID='" + maDocGia + "'", dataConnection);
                OleDbDataReader readerPM = cmd.ExecuteReader();
                List<CCTPhieuMuon> arrCTPM = new List<CCTPhieuMuon>();
                if (readerPM == null)
                {
                    throw new Exception("Không Tìm Thấy Phiếu Mượn Của Đọc Giả");
                }
                else
                {
                    int soCTPM = 0;
                    while (readerPM.Read())
                    {
                        pm.PhieuMuonID = readerPM.GetValue(0).ToString();
                        pm.DocGiaID=readerPM.GetValue(1).ToString();
                        pm.NgayLap = Convert.ToDateTime(readerPM.GetValue(2));

                        cmd = new OleDbCommand("SELECT COUNT(*) FROM ChiTietPhieuMuon WHERE PhieuMuonID='" + pm.PhieuMuonID + "' AND TrangThai='Dang muon'", dataConnection);
                        soCTPM += (int)cmd.ExecuteScalar();
                    }
                    return soCTPM;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Tìm Số CTPM By Mã Đọc Giả - CCTPhieuMuonDAL\n" + ex.ToString());
            }
        }

        #endregion
    }
}
